﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace UdrinkWater_Project.Officer
{
    public partial class DetialAllStock : System.Web.UI.Page
    {

        private string Connection = ConfigurationManager.ConnectionStrings["DB_Udrink"].ConnectionString;
        protected void Page_Load(object sender, EventArgs e)
        {
            string Date = Session["ProductDate"].ToString();
            DataTable dtInStock = GetAllList(Date);
            txtDate.Text = dtInStock.Rows[0]["Product_Date"].ToString();
            txtEmpId.Text = dtInStock.Rows[0]["Act_Officer"].ToString();
            txtEmpName.Text = dtInStock.Rows[0]["EmpName"].ToString();
            Session["list"] = dtInStock.Rows.Count;
            gv1.DataSource = dtInStock;
            gv1.DataBind();
        }
        private DataTable GetAllList(string indate)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("SELECT *, ROW_NUMBER() OVER(ORDER BY Product_Date) Row ");
            strSQL.Append("FROM (SELECT TB_StockProductIN.Product_Date, TB_StockProductIN.Product_ID, TB_Product.Product_name, TB_Unit_Category.Unit_Name, TB_StockProductIN.Product_Count, TB_StockProductIN.Act_Officer,");
            strSQL.Append("(TB_Employee.Prefix +''+ TB_Employee.Firstname +' '+ TB_Employee.Lastname) as EmpName,'เข้าคลัง' AS Status ");
            strSQL.Append("FROM TB_StockProductIN ");
            strSQL.Append("LEFT JOIN TB_Employee ON TB_StockProductIN.Act_Officer = TB_Employee.ID_Card ");
            strSQL.Append("LEFT JOIN TB_Product ON TB_StockProductIN.Product_ID = TB_Product.Product_ID ");
            strSQL.Append("LEFT JOIN TB_Unit_Category ON TB_Product.Unit = TB_Unit_Category.Unit_ID");
            strSQL.Append(" WHERE TB_StockProductIN.Product_Date ='" + indate + "' ");
            strSQL.Append(" UNION ");
            strSQL.Append("SELECT TB_StockProductOUT.Product_Date, TB_StockProductOUT.Product_ID, TB_Product.Product_name, TB_Unit_Category.Unit_Name, TB_StockProductOUT.Product_Count, TB_StockProductOUT.Act_Officer, ");
            strSQL.Append("(TB_Employee.Prefix +''+ TB_Employee.Firstname +' '+ TB_Employee.Lastname) as EmpName,'ออกคลัง' AS Status ");
            strSQL.Append("FROM TB_Product ");
            strSQL.Append("LEFT JOIN TB_Unit_Category ON TB_Product.Unit = TB_Unit_Category.Unit_ID LEFT JOIN ");
            strSQL.Append("TB_StockProductOUT ");
            strSQL.Append("LEFT JOIN TB_Employee ON TB_StockProductOUT.Act_Officer = TB_Employee.ID_Card ON TB_Product.Product_ID = TB_StockProductOUT.Product_ID ");
            strSQL.Append("WHERE TB_StockProductOUT.Product_Date  ='" + indate + "') a");
            strSQL.Append(" order by Product_ID asc");
            string sqltext = strSQL.ToString();
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds.Tables[0];
        }
        protected void ASPxButton1_Click(object sender, EventArgs e)
        {
            Session["StockDateID"] = Session["ProductDate"].ToString();
            Response.Redirect("StockReport.aspx");
        }
    }
}